﻿namespace KeppelLand.Controllers
{
    using KeppelLand.Entities;
    using KeppelLand.Helpers;
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;
    
    [Serializable]
    public class ClaimException :  Exception
    {
        public string Message { get; set; }
        public ClaimException(string message)
        {
            this.Message = message;
        }
        public override void GetObjectData(System.Runtime.Serialization.SerializationInfo info, System.Runtime.Serialization.StreamingContext context)
        {
            base.GetObjectData(info, context);
        }
    }

    /// <summary>
    /// Summary description for ClaimController
    /// </summary>
    public class ClaimController
    {
        public ClaimController()
        {
        }

        public static Claim GetClaim(string domainName, string id)
        {
            Claim claim = null;
            
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim WHERE EntryOwner = @EntryOwner AND Id = @Id;";
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                cmd.Parameters.AddWithValue("@ID", id);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claim = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),
                        
                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),
                        Remark = r.Field<string>("Remark"),

                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),

                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),

                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),

                        Exported = r.Field<string>("Exported")

                    }).FirstOrDefault();
                }
            }

            return claim;
        }

        public static List<Claim> ClaimDataTableToList(System.Data.DataTable dt)
        {
            List<Claim> claimList = null;

            if ((dt != null) && (dt.Rows.Count > 0))
            {
                claimList = dt.AsEnumerable().Select(r => new Claim
                {
                    Id = r.Field<int>("Id"),
                    EntryOwner = r.Field<string>("EntryOwner"),
                    EntryDateTime = r.Field<DateTime>("EntryDateTime"),

                    ClaimDate = r.Field<DateTime>("ClaimDate"),
                    StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                    EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                    StartOverTime = r.Field<DateTime?>("StartOverTime"),
                    EndOverTime = r.Field<DateTime?>("EndOverTime"),

                    OT1_5 = r.Field<decimal?>("OT1_5"),
                    OT2_0 = r.Field<decimal?>("OT2_0"),
                    PH = r.Field<bool?>("PH"),
                    RD4 = r.Field<bool?>("RD4"),
                    RD8 = r.Field<bool?>("RD8"),
                    AlwMeal = r.Field<decimal?>("AlwMeal"),
                    AlwShft = r.Field<decimal?>("AlwShft"),
                    ShiftType = r.Field<string>("ShiftType"),
                    Remark = r.Field<string>("Remark"),

                    Status = r.Field<byte?>("Status"),
                    SubmissionDate = r.Field<DateTime?>("SubmissionDate"),

                    RecommenderId = r.Field<string>("RecommenderId"),
                    RecommendDate = r.Field<DateTime?>("RecommendDate"),
                    ApproverId = r.Field<string>("ApproverId"),
                    ApproveDate = r.Field<DateTime?>("ApproveDate"),

                    RejectorId = r.Field<string>("RejectorId"),
                    RejectDate = r.Field<DateTime?>("RejectDate"),
                    RejectReason = r.Field<string>("RejectReason"),

                    Exported = r.Field<string>("Exported"),

                }).ToList();
            }

            return claimList;
        }

        public static DataTable GetBlankClaimDataTable(string isoDate)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet("Claim_DataSet");
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                cmd.CommandText = @"
WITH DateRange AS
(
    SELECT CONVERT(DATETIME, @IsoDate, 121) AS dr_date
    UNION ALL
    SELECT DATEADD([DAY], 1, dr_date)
    FROM DateRange
    WHERE DATEADD([DAY], 1, dr_date) < DATEADD([MONTH], 1, CONVERT(DATETIME, @IsoDate, 121))
)
SELECT  DISTINCT
        NULL AS 'Id',
        NULL AS 'EntryOwner',
        NULL AS 'EntryDateTime',
        dr.dr_date AS 'ClaimDate',
        NULL AS 'StartWorkTime',
        NULL AS 'EndWorkTime',
        NULL AS 'StartOverTime',
        NULL AS 'EndOverTime',
        NULL AS 'OT1_5',
        NULL AS 'OT2_0',
        NULL AS 'PH',
        NULL AS 'RD4',
        NULL AS 'RD8',
        NULL AS 'AlwShft',
        NULL AS 'AlwMeal',
        NULL AS 'ShiftType',
        NULL AS 'Remark',
        NULL AS 'Status',
        NULL AS 'SubmissionDate',
        NULL AS 'RecommenderId',
        NULL AS 'RecommendDate',
        NULL AS 'ApproverId',
        NULL AS 'ApproveDate',
        NULL AS 'RejectorId',
        NULL AS 'RejectDate',
        NULL AS 'RejectReason',
        NULL AS 'ReportingPeriodId',
        NULL AS 'Exported'
FROM    DateRange dr, Claim c
ORDER BY dr.dr_date";
                cmd.Parameters.AddWithValue("@IsoDate", isoDate);
                
                da.Fill(ds, "Claim_DataTable");
                dtResult = ds.Tables["Claim_DataTable"];
            }

            return dtResult;
        }

        public static DataTable GetClaimDataTable(string domainName)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet("Claim_DataSet");
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                cmd.CommandText = "SELECT * FROM Claim WHERE EntryOwner = @EntryOwner ORDER BY ClaimDate, StartWorkTime, EndWorkTime, StartOverTime, EndOverTime;";
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                da.Fill(ds, "Claim_DataTable");
                dtResult = ds.Tables["Claim_DataTable"];
            }

            return dtResult;
        }

        public static DataTable GetClaimDataTable(string domainName, string where_clause_modifications)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet("Claim_DataSet");
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                cmd.CommandText = "SELECT * FROM Claim WHERE EntryOwner = @EntryOwner " + where_clause_modifications + " ORDER BY ClaimDate, StartWorkTime, EndWorkTime, StartOverTime, EndOverTime;";
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                da.Fill(ds, "Claim_DataTable");
                dtResult = ds.Tables["Claim_DataTable"];
            }

            return dtResult;
        }

        public static DataTable GetClaimDataTable(string domainName, ClaimStatus status)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet("Claim_DataSet");
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                cmd.CommandText = "SELECT * FROM Claim WHERE EntryOwner = @EntryOwner AND Status = @Status ORDER BY ClaimDate, StartWorkTime, EndWorkTime, StartOverTime, EndOverTime;";
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                cmd.Parameters.AddWithValue("@Status", status);
                
                da.Fill(ds, "Claim_DataTable");
                dtResult = ds.Tables["Claim_DataTable"];
            }

            return dtResult;
        }

        public static DataTable GetClaimSubmittedToHRDataTable(string filter)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet("Claim_DataSet");
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                cmd.CommandText = @"
SELECT
c.Id,
b.NameLegal AS 'EntryOwner',
c.EntryDateTime,
c.ClaimDate,
c.StartWorkTime,
c.EndWorkTime,
c.StartOverTime,
c.EndOverTime,
c.BreakHours,
c.OT1_5,
c.OT2_0,
c.PH,
c.RD4,
c.RD8,
c.AlwShft,
c.AlwMeal,
c.ShiftType,
c.Remark,
c.Status,
c.SubmissionDate,
c.RecommenderId,
c.RecommendDate,
c.ApproverId,
c.ApproveDate,
c.RejectorId,
c.RejectDate,
c.RejectReason,
c.ReportingPeriodId,
c.Exported,
b.Section
FROM    Claim c
LEFT OUTER JOIN B2E b
ON c.EntryOwner = b.EmployeeMaster
WHERE ReportingPeriodId = (SELECT Id FROM ReportingPeriod WHERE Locked = 1) AND (c.Status = @ClaimStatus)

";
                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.SUBMITTED_TO_HR);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND c.EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ',')) ORDER BY b.NameLegal;";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + "ORDER BY b.NameLegal;";
                }

                da.Fill(ds, "Claim_DataTable");
                dtResult = ds.Tables["Claim_DataTable"];
            }

            return dtResult;
        }

        public static DataTable GetClaimPendingApprovalDataTable(string domainName, string filter)
        {
            string employeeMaster = KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName);
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet("Claim_DataSet");
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = @"
SELECT
c.Id,
b.NameLegal AS 'EntryOwner',
c.EntryDateTime,
c.ClaimDate,
c.StartWorkTime,
c.EndWorkTime,
c.StartOverTime,
c.EndOverTime,
c.BreakHours,
c.OT1_5,
c.OT2_0,
c.PH,
c.RD4,
c.RD8,
c.AlwShft,
c.AlwMeal,
c.ShiftType,
c.Remark,
c.Status,
c.SubmissionDate,
c.RecommenderId,
c.RecommendDate,
c.ApproverId,
c.ApproveDate,
c.RejectorId,
c.RejectDate,
c.RejectReason,
c.ReportingPeriodId,
c.Exported
FROM    Claim c
LEFT OUTER JOIN B2E b
ON c.EntryOwner = b.EmployeeMaster
WHERE ((c.RecommenderId = @RecommenderId AND c.Status = @RecommendingStatus) OR (c.ApproverId = @ApproverId AND c.Status = @ApprovingStatus))";
                cmd.Parameters.AddWithValue("@RecommenderId", employeeMaster);
                cmd.Parameters.AddWithValue("@ApproverId", employeeMaster);
                cmd.Parameters.AddWithValue("@RecommendingStatus", ClaimStatus.SUBMITTED_TO_RO);
                cmd.Parameters.AddWithValue("@ApprovingStatus", ClaimStatus.SUBMITTED_TO_AO);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ','));";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + ";";
                }

                da.Fill(ds, "Claim_DataTable");
                dtResult = ds.Tables["Claim_DataTable"];
            }

            return dtResult;
        }

        public static List<Claim> GetRetrievableClaimList(string domainName)
        {
            List<Claim> claimList = new List<Claim>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim WHERE EntryOwner = @EntryOwner AND ((Status = 1) OR (Status = 3 AND RecommenderId IS NULL)) ORDER BY ClaimDate, StartWorkTime, EndWorkTime, StartOverTime, EndOverTime;";
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),

                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),
                        Remark = r.Field<string>("Remark"),

                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),

                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),

                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),

                        Exported = r.Field<string>("Exported"),

                    }).ToList();
                }
            }

            return claimList;
        }

        public static List<Claim> GetClaimList(string domainName)
        {
            List<Claim> claimList = new List<Claim>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim WHERE EntryOwner = @EntryOwner ORDER BY ClaimDate, StartWorkTime, EndWorkTime, StartOverTime, EndOverTime;";
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),

                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),
                        Remark = r.Field<string>("Remark"),

                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),

                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),

                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),

                        Exported = r.Field<string>("Exported"),

                    }).ToList();
                }
            }

            return claimList;
        }

        public static List<Claim> GetClaimList(string domainName, string where_clause_modifications)
        {
            List<Claim> claimList = new List<Claim>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim WHERE EntryOwner = @EntryOwner " + where_clause_modifications + " ORDER BY ClaimDate, StartWorkTime, EndWorkTime, StartOverTime, EndOverTime;";
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),

                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),
                        Remark = r.Field<string>("Remark"),

                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),

                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),

                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),

                        Exported = r.Field<string>("Exported"),
                        HRClearedDate = r.Field<DateTime?>("HRClearedDate")

                    }).ToList();
                }
            }

            return claimList;
        }

        public static List<Claim> GetClaimList(string domainName, ClaimStatus status)
        {
            List<Claim> claimList = new List<Claim>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim WHERE EntryOwner = @EntryOwner AND Status = @Status ORDER BY ClaimDate, StartWorkTime, EndWorkTime, StartOverTime, EndOverTime;";
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                cmd.Parameters.AddWithValue("@Status", status);

                //cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(User.Identity.Name));

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),

                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),
                        Remark = r.Field<string>("Remark"),

                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),

                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),

                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),

                        Exported = r.Field<string>("Exported"),

                    }).ToList();
                }
            }

            return claimList;
        }

        public static List<Claim> GetClaimHistoryList(string domainName, ClaimStatus status, string filter)
        {
            List<Claim> claimList = new List<Claim>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim WHERE Status = @Status ";

                cmd.CommandText = @"SELECT * FROM Claim 
INNER JOIN B2E 
ON Claim.EntryOwner = B2E.EmployeeMaster
WHERE Status = @Status AND B2E.Section = (SELECT Section FROM B2E WHERE EmployeeMaster = @EmployeeMaster) AND AccessLevel = 'EMP'";

                cmd.Parameters.AddWithValue("@Status", status);
                cmd.Parameters.AddWithValue("@EmployeeMaster", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ',')) ORDER BY ClaimDate, StartWorkTime, EndWorkTime, StartOverTime, EndOverTime;";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + " ORDER BY ClaimDate, StartWorkTime, EndWorkTime, StartOverTime, EndOverTime;";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),

                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),
                        Remark = r.Field<string>("Remark"),

                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),

                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),

                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),

                        Exported = r.Field<string>("Exported"),
                        Section = r.Field<string>("Section")

                    }).ToList();
                }
            }

            return claimList;
        }

        public static ClaimSummary GetRetrievableClaimSummary(string domainName)
        {
            ClaimSummary result = null;
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT    ISNULL(CONVERT(DECIMAL(8, 2), SUM(DATEDIFF(minute, StartOverTime, EndOverTime)) / 60.0), 0) as 'Total_OT_Hours',
            ISNULL(SUM(ISNULL(BreakHours, 0)), 0)   AS 'Break_Hours',
            ISNULL(SUM(ISNULL(OT1_5, 0)), 0)   AS 'OT1_5', 
            ISNULL(SUM(ISNULL(OT2_0, 0)), 0)   AS 'OT2_0', 
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, PH), 0)), 0) AS 'PH',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD4), 0)), 0) AS 'RD4',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD8), 0)), 0) AS 'RD8',
            ISNULL(SUM(ISNULL(AlwMeal, 0)), 0) AS 'AlwMeal', 
            ISNULL(SUM(ISNULL(AlwShft, 0)), 0) AS 'AlwShft'
FROM    Claim 
WHERE EntryOwner = @EntryOwner AND ((Status = 1) OR (Status = 3 AND RecommenderId IS NULL));";
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    result = ds.Tables[0].AsEnumerable().Select(r => new ClaimSummary
                    {
                        Total_OT_Hours = r.Field<decimal>("Total_OT_Hours"),
                        Break_Hours = r.Field<decimal>("Break_Hours"),
                        OT1_5 = r.Field<decimal>("OT1_5"),
                        OT2_0 = r.Field<decimal>("OT2_0"),
                        PH = r.Field<int>("PH"),
                        RD4 = r.Field<int>("RD4"),
                        RD8 = r.Field<int>("RD8"),
                        AlwMeal = r.Field<decimal>("AlwMeal"),
                        AlwShft = r.Field<decimal>("AlwShft"),
                    }).FirstOrDefault();
                }
            }

            return result;
        }

        public static ClaimSummary GetClaimSummary(string domainName)
        {
            ClaimSummary result = null;
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT    ISNULL(CONVERT(DECIMAL(8, 2), SUM(DATEDIFF(minute, StartOverTime, EndOverTime)) / 60.0), 0) as 'Total_OT_Hours',
            ISNULL(SUM(ISNULL(BreakHours, 0)), 0)   AS 'Break_Hours',
            ISNULL(SUM(ISNULL(OT1_5, 0)), 0)   AS 'OT1_5', 
            ISNULL(SUM(ISNULL(OT2_0, 0)), 0)   AS 'OT2_0', 
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, PH), 0)), 0) AS 'PH',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD4), 0)), 0) AS 'RD4',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD8), 0)), 0) AS 'RD8',
            ISNULL(SUM(ISNULL(AlwMeal, 0)), 0) AS 'AlwMeal', 
            ISNULL(SUM(ISNULL(AlwShft, 0)), 0) AS 'AlwShft'
FROM    Claim 
WHERE EntryOwner = @EntryOwner ;";
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    result = ds.Tables[0].AsEnumerable().Select(r => new ClaimSummary
                    {
                        Total_OT_Hours = r.Field<decimal>("Total_OT_Hours"),
                        Break_Hours = r.Field<decimal>("Break_Hours"),
                        OT1_5 = r.Field<decimal>("OT1_5"),
                        OT2_0 = r.Field<decimal>("OT2_0"),
                        PH = r.Field<int>("PH"),
                        RD4 = r.Field<int>("RD4"),
                        RD8 = r.Field<int>("RD8"),
                        AlwMeal = r.Field<decimal>("AlwMeal"),
                        AlwShft = r.Field<decimal>("AlwShft"),
                    }).FirstOrDefault();
                }
            }

            return result;
        }

        public static ClaimSummary GetClaimSummary(string domainName, ClaimStatus status)
        {
            ClaimSummary result = null;
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT    ISNULL(CONVERT(DECIMAL(8, 2), SUM(DATEDIFF(minute, StartOverTime, EndOverTime)) / 60.0), 0) as 'Total_OT_Hours',
            ISNULL(SUM(ISNULL(BreakHours, 0)), 0)   AS 'Break_Hours',
            ISNULL(SUM(ISNULL(OT1_5, 0)), 0)   AS 'OT1_5', 
            ISNULL(SUM(ISNULL(OT2_0, 0)), 0)   AS 'OT2_0', 
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, PH), 0)), 0) AS 'PH',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD4), 0)), 0) AS 'RD4',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD8), 0)), 0) AS 'RD8',
            ISNULL(SUM(ISNULL(AlwMeal, 0)), 0) AS 'AlwMeal', 
            ISNULL(SUM(ISNULL(AlwShft, 0)), 0) AS 'AlwShft'
FROM    Claim 
WHERE EntryOwner = @EntryOwner AND Status = @Status;";
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                cmd.Parameters.AddWithValue("@Status", (int)status);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    result = ds.Tables[0].AsEnumerable().Select(r => new ClaimSummary
                    {
                        Total_OT_Hours = r.Field<decimal>("Total_OT_Hours"),
                        Break_Hours = r.Field<decimal>("Break_Hours"),
                        OT1_5 = r.Field<decimal>("OT1_5"),
                        OT2_0 = r.Field<decimal>("OT2_0"),
                        PH = r.Field<int>("PH"),
                        RD4 = r.Field<int>("RD4"),
                        RD8 = r.Field<int>("RD8"),
                        AlwMeal = r.Field<decimal>("AlwMeal"),
                        AlwShft = r.Field<decimal>("AlwShft"),
                    }).FirstOrDefault();
                }
            }

            return result;
        }

        public static ClaimSummary GetClaimHistorySummary(string domainName, ClaimStatus status, string filter)
        {
            ClaimSummary result = null;
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT    ISNULL(CONVERT(DECIMAL(8, 2), SUM(DATEDIFF(minute, StartOverTime, EndOverTime)) / 60.0), 0) as 'Total_OT_Hours',
            ISNULL(SUM(ISNULL(BreakHours, 0)), 0)   AS 'Break_Hours',
            ISNULL(SUM(ISNULL(OT1_5, 0)), 0)   AS 'OT1_5', 
            ISNULL(SUM(ISNULL(OT2_0, 0)), 0)   AS 'OT2_0', 
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, PH), 0)), 0) AS 'PH',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD4), 0)), 0) AS 'RD4',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD8), 0)), 0) AS 'RD8',
            ISNULL(SUM(ISNULL(AlwMeal, 0)), 0) AS 'AlwMeal', 
            ISNULL(SUM(ISNULL(AlwShft, 0)), 0) AS 'AlwShft'
FROM    Claim INNER JOIN B2E ON Claim.EntryOwner = B2E.EmployeeMaster 
WHERE Status = @Status AND B2E.Section = (SELECT Section FROM B2E WHERE EmployeeMaster = @EmployeeMaster) AND AccessLevel = 'EMP'";
                cmd.Parameters.AddWithValue("@EmployeeMaster", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                cmd.Parameters.AddWithValue("@Status", (int)status);


                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ',')) ;";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + " ;";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    result = ds.Tables[0].AsEnumerable().Select(r => new ClaimSummary
                    {
                        Total_OT_Hours = r.Field<decimal>("Total_OT_Hours"),
                        Break_Hours = r.Field<decimal>("Break_Hours"),
                        OT1_5 = r.Field<decimal>("OT1_5"),
                        OT2_0 = r.Field<decimal>("OT2_0"),
                        PH = r.Field<int>("PH"),
                        RD4 = r.Field<int>("RD4"),
                        RD8 = r.Field<int>("RD8"),
                        AlwMeal = r.Field<decimal>("AlwMeal"),
                        AlwShft = r.Field<decimal>("AlwShft"),
                    }).FirstOrDefault();
                }
            }

            return result;
        }

        public static ClaimSummary GetClaimSummaryPendingRecommendationOrApproval(string domainName, string filter)
        {
            ClaimSummary result = null;
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT    ISNULL(CONVERT(DECIMAL(8, 2), SUM(DATEDIFF(minute, StartOverTime, EndOverTime)) / 60.0), 0) as 'Total_OT_Hours',
            ISNULL(SUM(ISNULL(BreakHours, 0)), 0)   AS 'Break_Hours',
            ISNULL(SUM(ISNULL(OT1_5, 0)), 0)   AS 'OT1_5', 
            ISNULL(SUM(ISNULL(OT2_0, 0)), 0)   AS 'OT2_0', 
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, PH), 0)), 0) AS 'PH',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD4), 0)), 0) AS 'RD4',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD8), 0)), 0) AS 'RD8',
            ISNULL(SUM(ISNULL(AlwMeal, 0)), 0) AS 'AlwMeal', 
            ISNULL(SUM(ISNULL(AlwShft, 0)), 0) AS 'AlwShft'
FROM    Claim 
WHERE ((RecommenderId = @RecommenderId AND Status = @RecommendingStatus) OR (ApproverId = @ApproverId AND Status = @ApprovingStatus)) ";
//WHERE   (RecommenderId = @Officer OR ApproverId = @Officer) AND (Status > @DraftStatus AND Status < @SubmittedToHRStatus)";

                //cmd.Parameters.AddWithValue("@Officer", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                //cmd.Parameters.AddWithValue("@DraftStatus", ClaimStatus.DRAFT);
                //cmd.Parameters.AddWithValue("@SubmittedToHRStatus", ClaimStatus.SUBMITTED_TO_HR);
                string employeeMaster = KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName);
                cmd.Parameters.AddWithValue("@RecommenderId", employeeMaster);
                cmd.Parameters.AddWithValue("@ApproverId", employeeMaster);
                cmd.Parameters.AddWithValue("@RecommendingStatus", ClaimStatus.SUBMITTED_TO_RO);
                cmd.Parameters.AddWithValue("@ApprovingStatus", ClaimStatus.SUBMITTED_TO_AO);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ','));";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + ";";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    result = ds.Tables[0].AsEnumerable().Select(r => new ClaimSummary
                    {
                        Total_OT_Hours = r.Field<decimal>("Total_OT_Hours"),
                        Break_Hours = r.Field<decimal>("Break_Hours"),
                        OT1_5 = r.Field<decimal>("OT1_5"),
                        OT2_0 = r.Field<decimal>("OT2_0"),
                        PH = r.Field<int>("PH"),
                        RD4 = r.Field<int>("RD4"),
                        RD8 = r.Field<int>("RD8"),
                        AlwMeal = r.Field<decimal>("AlwMeal"),
                        AlwShft = r.Field<decimal>("AlwShft"),
                    }).FirstOrDefault();
                }
            }

            return result;
        }

        public static ClaimSummary GetAllClaimSummarySubmittedInSection(string domainName, string filter, string section)
        {
            ClaimSummary result = null;
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT    ISNULL(CONVERT(DECIMAL(8, 2), SUM(DATEDIFF(minute, StartOverTime, EndOverTime)) / 60.0), 0) as 'Total_OT_Hours',
            ISNULL(SUM(ISNULL(BreakHours, 0)), 0)   AS 'Break_Hours',
            ISNULL(SUM(ISNULL(OT1_5, 0)), 0)   AS 'OT1_5', 
            ISNULL(SUM(ISNULL(OT2_0, 0)), 0)   AS 'OT2_0', 
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, PH), 0)), 0) AS 'PH',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD4), 0)), 0) AS 'RD4',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD8), 0)), 0) AS 'RD8',
            ISNULL(SUM(ISNULL(AlwMeal, 0)), 0) AS 'AlwMeal', 
            ISNULL(SUM(ISNULL(AlwShft, 0)), 0) AS 'AlwShft'
FROM    Claim 
WHERE   Status > @SubmittedToHRStatus AND EntryOwner IN (SELECT EmployeeMaster FROM B2E WHERE Section = @B2E_Section) ";

                cmd.Parameters.AddWithValue("@SubmittedToHRStatus", ClaimStatus.DRAFT);
                cmd.Parameters.AddWithValue("@B2E_Section", section);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ','));";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + ";";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    result = ds.Tables[0].AsEnumerable().Select(r => new ClaimSummary
                    {
                        Total_OT_Hours = r.Field<decimal>("Total_OT_Hours"),
                        Break_Hours = r.Field<decimal>("Break_Hours"),
                        OT1_5 = r.Field<decimal>("OT1_5"),
                        OT2_0 = r.Field<decimal>("OT2_0"),
                        PH = r.Field<int>("PH"),
                        RD4 = r.Field<int>("RD4"),
                        RD8 = r.Field<int>("RD8"),
                        AlwMeal = r.Field<decimal>("AlwMeal"),
                        AlwShft = r.Field<decimal>("AlwShft"),
                    }).FirstOrDefault();
                }
            }

            return result;
        }

        public static ClaimSummary GetAllClaimSummarySubmittedInSection(string domainName, string filter, string section, string whereClause)
        {
            ClaimSummary result = null;
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT    ISNULL(CONVERT(DECIMAL(8, 2), SUM(DATEDIFF(minute, StartOverTime, EndOverTime)) / 60.0), 0) as 'Total_OT_Hours',
            ISNULL(SUM(ISNULL(BreakHours, 0)), 0)   AS 'Break_Hours',
            ISNULL(SUM(ISNULL(OT1_5, 0)), 0)   AS 'OT1_5', 
            ISNULL(SUM(ISNULL(OT2_0, 0)), 0)   AS 'OT2_0', 
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, PH), 0)), 0) AS 'PH',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD4), 0)), 0) AS 'RD4',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD8), 0)), 0) AS 'RD8',
            ISNULL(SUM(ISNULL(AlwMeal, 0)), 0) AS 'AlwMeal', 
            ISNULL(SUM(ISNULL(AlwShft, 0)), 0) AS 'AlwShft'
FROM    Claim 
WHERE   Status > @SubmittedToHRStatus AND EntryOwner IN (SELECT EmployeeMaster FROM B2E WHERE Section = @B2E_Section) " + whereClause;

                cmd.Parameters.AddWithValue("@SubmittedToHRStatus", ClaimStatus.DRAFT);
                cmd.Parameters.AddWithValue("@B2E_Section", section);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ','));";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + ";";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    result = ds.Tables[0].AsEnumerable().Select(r => new ClaimSummary
                    {
                        Total_OT_Hours = r.Field<decimal>("Total_OT_Hours"),
                        Break_Hours = r.Field<decimal>("Break_Hours"),
                        OT1_5 = r.Field<decimal>("OT1_5"),
                        OT2_0 = r.Field<decimal>("OT2_0"),
                        PH = r.Field<int>("PH"),
                        RD4 = r.Field<int>("RD4"),
                        RD8 = r.Field<int>("RD8"),
                        AlwMeal = r.Field<decimal>("AlwMeal"),
                        AlwShft = r.Field<decimal>("AlwShft"),
                    }).FirstOrDefault();
                }
            }

            return result;
        }

        public static ClaimSummary GetSecretaryAllClaimSummarySubmittedInSection(string domainName, string filter, string section)
        {
            ClaimSummary result = null;
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT    ISNULL(CONVERT(DECIMAL(8, 2), SUM(DATEDIFF(minute, StartOverTime, EndOverTime)) / 60.0), 0) as 'Total_OT_Hours',
            ISNULL(SUM(ISNULL(BreakHours, 0)), 0)   AS 'Break_Hours',
            ISNULL(SUM(ISNULL(OT1_5, 0)), 0)   AS 'OT1_5', 
            ISNULL(SUM(ISNULL(OT2_0, 0)), 0)   AS 'OT2_0', 
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, PH), 0)), 0) AS 'PH',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD4), 0)), 0) AS 'RD4',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD8), 0)), 0) AS 'RD8',
            ISNULL(SUM(ISNULL(AlwMeal, 0)), 0) AS 'AlwMeal', 
            ISNULL(SUM(ISNULL(AlwShft, 0)), 0) AS 'AlwShft'
FROM    Claim 
WHERE   Status = @SubmittedToHRStatus AND EntryOwner IN (SELECT EmployeeMaster FROM B2E WHERE Section = @B2E_Section) ";

                cmd.Parameters.AddWithValue("@SubmittedToHRStatus", ClaimStatus.SUBMITTED_TO_RO);
                cmd.Parameters.AddWithValue("@B2E_Section", section);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ','));";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + ";";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    result = ds.Tables[0].AsEnumerable().Select(r => new ClaimSummary
                    {
                        Total_OT_Hours = r.Field<decimal>("Total_OT_Hours"),
                        Break_Hours = r.Field<decimal>("Break_Hours"),
                        OT1_5 = r.Field<decimal>("OT1_5"),
                        OT2_0 = r.Field<decimal>("OT2_0"),
                        PH = r.Field<int>("PH"),
                        RD4 = r.Field<int>("RD4"),
                        RD8 = r.Field<int>("RD8"),
                        AlwMeal = r.Field<decimal>("AlwMeal"),
                        AlwShft = r.Field<decimal>("AlwShft"),
                    }).FirstOrDefault();
                }
            }

            return result;
        }

        public static ClaimSummary GetAllClaimSummarySubmitted(string domainName, string filter)
        {
            ClaimSummary result = null;
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT    ISNULL(CONVERT(DECIMAL(8, 2), SUM(DATEDIFF(minute, StartOverTime, EndOverTime)) / 60.0), 0) as 'Total_OT_Hours',
            ISNULL(SUM(ISNULL(BreakHours, 0)), 0)   AS 'Break_Hours',
            ISNULL(SUM(ISNULL(OT1_5, 0)), 0)   AS 'OT1_5', 
            ISNULL(SUM(ISNULL(OT2_0, 0)), 0)   AS 'OT2_0', 
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, PH), 0)), 0) AS 'PH',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD4), 0)), 0) AS 'RD4',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD8), 0)), 0) AS 'RD8',
            ISNULL(SUM(ISNULL(AlwMeal, 0)), 0) AS 'AlwMeal', 
            ISNULL(SUM(ISNULL(AlwShft, 0)), 0) AS 'AlwShft'
FROM    Claim 
WHERE   Status > @SubmittedToHRStatus";

                cmd.Parameters.AddWithValue("@SubmittedToHRStatus", ClaimStatus.DRAFT);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ','));";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + ";";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    result = ds.Tables[0].AsEnumerable().Select(r => new ClaimSummary
                    {
                        Total_OT_Hours = r.Field<decimal>("Total_OT_Hours"),
                        Break_Hours = r.Field<decimal>("Break_Hours"),
                        OT1_5 = r.Field<decimal>("OT1_5"),
                        OT2_0 = r.Field<decimal>("OT2_0"),
                        PH = r.Field<int>("PH"),
                        RD4 = r.Field<int>("RD4"),
                        RD8 = r.Field<int>("RD8"),
                        AlwMeal = r.Field<decimal>("AlwMeal"),
                        AlwShft = r.Field<decimal>("AlwShft"),
                    }).FirstOrDefault();
                }
            }

            return result;
        }

        public static ClaimSummary GetAllClaimSummarySubmitted(string domainName, string filter, string whereClause)
        {
            ClaimSummary result = null;
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT    ISNULL(CONVERT(DECIMAL(8, 2), SUM(DATEDIFF(minute, StartOverTime, EndOverTime)) / 60.0), 0) as 'Total_OT_Hours',
            ISNULL(SUM(ISNULL(BreakHours, 0)), 0)   AS 'Break_Hours',
            ISNULL(SUM(ISNULL(OT1_5, 0)), 0)   AS 'OT1_5', 
            ISNULL(SUM(ISNULL(OT2_0, 0)), 0)   AS 'OT2_0', 
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, PH), 0)), 0) AS 'PH',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD4), 0)), 0) AS 'RD4',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD8), 0)), 0) AS 'RD8',
            ISNULL(SUM(ISNULL(AlwMeal, 0)), 0) AS 'AlwMeal', 
            ISNULL(SUM(ISNULL(AlwShft, 0)), 0) AS 'AlwShft'
FROM    Claim 
WHERE   Status > @SubmittedToHRStatus " + whereClause;

                cmd.Parameters.AddWithValue("@SubmittedToHRStatus", ClaimStatus.DRAFT);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ','));";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + ";";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    result = ds.Tables[0].AsEnumerable().Select(r => new ClaimSummary
                    {
                        Total_OT_Hours = r.Field<decimal>("Total_OT_Hours"),
                        Break_Hours = r.Field<decimal>("Break_Hours"),
                        OT1_5 = r.Field<decimal>("OT1_5"),
                        OT2_0 = r.Field<decimal>("OT2_0"),
                        PH = r.Field<int>("PH"),
                        RD4 = r.Field<int>("RD4"),
                        RD8 = r.Field<int>("RD8"),
                        AlwMeal = r.Field<decimal>("AlwMeal"),
                        AlwShft = r.Field<decimal>("AlwShft"),
                    }).FirstOrDefault();
                }
            }

            return result;
        }

        public static ClaimSummary GetClaimSummarySubmittedToHR(string domainName, string filter)
        {
            ClaimSummary result = null;
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT    ISNULL(CONVERT(DECIMAL(8, 2), SUM(DATEDIFF(minute, StartOverTime, EndOverTime)) / 60.0), 0) as 'Total_OT_Hours',
            ISNULL(SUM(ISNULL(BreakHours, 0)), 0)   AS 'Break_Hours',
            ISNULL(SUM(ISNULL(OT1_5, 0)), 0)   AS 'OT1_5', 
            ISNULL(SUM(ISNULL(OT2_0, 0)), 0)   AS 'OT2_0', 
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, PH), 0)), 0) AS 'PH',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD4), 0)), 0) AS 'RD4',
            ISNULL(SUM(ISNULL(CONVERT(TINYINT, RD8), 0)), 0) AS 'RD8',
            ISNULL(SUM(ISNULL(AlwMeal, 0)), 0) AS 'AlwMeal', 
            ISNULL(SUM(ISNULL(AlwShft, 0)), 0) AS 'AlwShft'
FROM    Claim 
WHERE   ReportingPeriodId = (SELECT Id FROM ReportingPeriod WHERE Locked = 1) AND Status = @SubmittedToHRStatus";

                cmd.Parameters.AddWithValue("@SubmittedToHRStatus", ClaimStatus.SUBMITTED_TO_HR);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ','));";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + ";";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    result = ds.Tables[0].AsEnumerable().Select(r => new ClaimSummary
                    {
                        Total_OT_Hours = r.Field<decimal>("Total_OT_Hours"),
                        Break_Hours = r.Field<decimal>("Break_Hours"),
                        OT1_5 = r.Field<decimal>("OT1_5"),
                        OT2_0 = r.Field<decimal>("OT2_0"),
                        PH = r.Field<int>("PH"),
                        RD4 = r.Field<int>("RD4"),
                        RD8 = r.Field<int>("RD8"),
                        AlwMeal = r.Field<decimal>("AlwMeal"),
                        AlwShft = r.Field<decimal>("AlwShft"),
                    }).FirstOrDefault();
                }
            }

            return result;
        }

        public static List<Claim> GetClaimListPendingRecommendationOrApproval(string domainName, string filter)
        {
            string employeeMaster = KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName);

            List<Claim> claimList = new List<Claim>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim WHERE ((RecommenderId = @RecommenderId AND Status = @RecommendingStatus) OR (ApproverId = @ApproverId AND Status = @ApprovingStatus)) ";
                cmd.Parameters.AddWithValue("@RecommenderId", employeeMaster);
                cmd.Parameters.AddWithValue("@ApproverId", employeeMaster);
                cmd.Parameters.AddWithValue("@RecommendingStatus", ClaimStatus.SUBMITTED_TO_RO);
                cmd.Parameters.AddWithValue("@ApprovingStatus", ClaimStatus.SUBMITTED_TO_AO);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ',')) ORDER BY EntryOwner, ClaimDate;";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + " ORDER BY EntryOwner, ClaimDate;";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),
                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),

                        Remark = r.Field<string>("Remark"),
                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),
                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),
                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),
                        Exported = r.Field<string>("Exported"),

                    }).ToList();
                }
            }

            return claimList;
        }

        public static List<Claimant> GetClaimantListPendingRecommendationOrApproval(string domainName)
        {
            string employeeMaster = KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName);

            List<Claimant> claimantList = new List<Claimant>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT DISTINCT EntryOwner FROM Claim WHERE (RecommenderId = @RecommenderId AND Status = @RecommendingStatus) OR (ApproverId = @ApproverId AND Status = @ApprovingStatus);";
                cmd.Parameters.AddWithValue("@RecommenderId", employeeMaster);
                cmd.Parameters.AddWithValue("@ApproverId", employeeMaster);
                cmd.Parameters.AddWithValue("@RecommendingStatus", ClaimStatus.SUBMITTED_TO_RO);
                cmd.Parameters.AddWithValue("@ApprovingStatus", ClaimStatus.SUBMITTED_TO_AO);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimantList = ds.Tables[0].AsEnumerable().Select(r => new Claimant
                    {
                        EmployeeMaster = r.Field<string>("EntryOwner")
                    }).ToList();
                }
            }
            
            return claimantList;
        }

        public static List<Claim> GetAllClaimListSubmittedInSection(string filter, string section)
        {
            List<Claim> claimList = new List<Claim>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim WHERE (Status > @ClaimStatus) AND EntryOwner IN (SELECT EmployeeMaster FROM B2E WHERE Section = @B2E_Section) ";
                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.DRAFT);
                cmd.Parameters.AddWithValue("@B2E_Section", section);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ',')) ORDER BY EntryOwner, ClaimDate;";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + " ORDER BY EntryOwner, ClaimDate;";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),
                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),

                        Remark = r.Field<string>("Remark"),
                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),
                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),
                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),
                        Exported = r.Field<string>("Exported"),

                    }).ToList();
                }
            }

            return claimList;
        }

        public static List<Claim> GetAllClaimListSubmittedInSection(string filter, string section, string whereClause)
        {
            List<Claim> claimList = new List<Claim>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim WHERE (Status > @ClaimStatus) AND EntryOwner IN (SELECT EmployeeMaster FROM B2E WHERE Section = @B2E_Section) " + whereClause;
                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.DRAFT);
                cmd.Parameters.AddWithValue("@B2E_Section", section);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ',')) ORDER BY EntryOwner, ClaimDate;";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + " ORDER BY EntryOwner, ClaimDate;";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),
                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),

                        Remark = r.Field<string>("Remark"),
                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),
                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),
                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),
                        Exported = r.Field<string>("Exported"),
                        HRClearedDate = r.Field<DateTime?>("HRClearedDate")

                    }).ToList();
                }
            }

            return claimList;
        }

        public static List<Claim> GetSecretaryAllClaimListSubmittedInSection(string filter, string section)
        {
            List<Claim> claimList = new List<Claim>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim WHERE (Status = @ClaimStatus) AND EntryOwner IN (SELECT EmployeeMaster FROM B2E WHERE Section = @B2E_Section) ";
                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.SUBMITTED_TO_RO);
                cmd.Parameters.AddWithValue("@B2E_Section", section);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ',')) ORDER BY EntryOwner, ClaimDate;";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + " ORDER BY EntryOwner, ClaimDate;";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),
                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),

                        Remark = r.Field<string>("Remark"),
                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),
                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),
                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),
                        Exported = r.Field<string>("Exported"),

                    }).ToList();
                }
            }

            return claimList;
        }

        public static List<Claim> GetAllClaimListSubmitted(string filter)
        {
            List<Claim> claimList = new List<Claim>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim c LEFT JOIN B2E b ON c.EntryOwner = b.EmployeeMaster WHERE (Status > @ClaimStatus)";
                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.DRAFT);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ',')) ORDER BY EntryOwner, ClaimDate;";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + " ORDER BY b.NameLegal, ClaimDate;";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),
                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),

                        Remark = r.Field<string>("Remark"),
                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),
                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),
                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),
                        Exported = r.Field<string>("Exported"),

                        Section = r.Field<string>("Section")

                    }).ToList();
                }
            }

            return claimList;
        }

        public static List<Claim> GetAllClaimListSubmitted(string filter, string whereClause)
        {
            List<Claim> claimList = new List<Claim>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim c LEFT JOIN B2E b ON c.EntryOwner = b.EmployeeMaster WHERE (Status > @ClaimStatus) " + whereClause;
                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.DRAFT);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ',')) ORDER BY EntryOwner, ClaimDate;";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + " ORDER BY b.NameLegal, ClaimDate;";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),
                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),

                        Remark = r.Field<string>("Remark"),
                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),
                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),
                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),
                        Exported = r.Field<string>("Exported"),

                        Section = r.Field<string>("Section"),
                        HRClearedDate = r.Field<DateTime?>("HRClearedDate")

                    }).ToList();
                }
            }

            return claimList;
        }

        public static List<Claim> GetClaimListSubmittedToHR(string filter)
        {
            List<Claim> claimList = new List<Claim>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim WHERE (Status = @ClaimStatus)";
                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.SUBMITTED_TO_HR);

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ',')) ORDER BY EntryOwner, ClaimDate;";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                else
                {
                    cmd.CommandText = cmd.CommandText + " ORDER BY EntryOwner, ClaimDate;";
                }

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),
                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),

                        Remark = r.Field<string>("Remark"),
                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),
                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),
                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),
                        Exported = r.Field<string>("Exported"),

                    }).ToList();
                }
            }

            return claimList;
        }

        public static List<Claim> GetClaimListSubmittedToHR(string filter, string section_filter)
        {
            List<Claim> claimList = new List<Claim>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                //cmd.CommandText = "SELECT * FROM Claim WHERE (Status = @ClaimStatus)";
                cmd.CommandText = @"SELECT * FROM Claim c
INNER JOIN B2E b
ON c.EntryOwner = b.EmployeeMaster 
WHERE ReportingPeriodId = (SELECT Id FROM ReportingPeriod WHERE Locked = 1) AND c.Status = @ClaimStatus ";

                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.SUBMITTED_TO_HR);

                if (!string.IsNullOrEmpty(section_filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND b.Section IN (SELECT data FROM dbo.fnSplitString(@SectionList, ',')) AND b.Section <> '' ";
                    cmd.Parameters.AddWithValue("@SectionList", section_filter);
                }

                if (!string.IsNullOrEmpty(filter.Trim()))
                {
                    cmd.CommandText = cmd.CommandText + " AND c.EntryOwner IN (SELECT data FROM dbo.fnSplitString(@IdList, ',')) ";
                    cmd.Parameters.AddWithValue("@IdList", filter);
                }
                
                cmd.CommandText = cmd.CommandText + " ORDER BY EntryOwner, ClaimDate;";
                

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new Claim
                    {
                        Id = r.Field<int>("Id"),
                        EntryOwner = r.Field<string>("EntryOwner"),
                        EntryDateTime = r.Field<DateTime>("EntryDateTime"),
                        ClaimDate = r.Field<DateTime>("ClaimDate"),
                        StartWorkTime = r.Field<DateTime?>("StartWorkTime"),
                        EndWorkTime = r.Field<DateTime?>("EndWorkTime"),
                        StartOverTime = r.Field<DateTime?>("StartOverTime"),
                        EndOverTime = r.Field<DateTime?>("EndOverTime"),
                        BreakHours = r.Field<decimal?>("BreakHours"),
                        OT1_5 = r.Field<decimal?>("OT1_5"),
                        OT2_0 = r.Field<decimal?>("OT2_0"),
                        PH = r.Field<bool?>("PH"),
                        RD4 = r.Field<bool?>("RD4"),
                        RD8 = r.Field<bool?>("RD8"),
                        AlwMeal = r.Field<decimal?>("AlwMeal"),
                        AlwShft = r.Field<decimal?>("AlwShft"),
                        ShiftType = r.Field<string>("ShiftType"),

                        Remark = r.Field<string>("Remark"),
                        Status = r.Field<byte?>("Status"),
                        SubmissionDate = r.Field<DateTime?>("SubmissionDate"),
                        RecommenderId = r.Field<string>("RecommenderId"),
                        RecommendDate = r.Field<DateTime?>("RecommendDate"),
                        ApproverId = r.Field<string>("ApproverId"),
                        ApproveDate = r.Field<DateTime?>("ApproveDate"),
                        RejectorId = r.Field<string>("RejectorId"),
                        RejectDate = r.Field<DateTime?>("RejectDate"),
                        RejectReason = r.Field<string>("RejectReason"),
                        Exported = r.Field<string>("Exported"),

                    }).ToList();
                }
            }

            return claimList;
        }

        public static List<Claimant> GetClaimantListSubmittedToSupervisor(string domainName)
        {
            List<Claimant> claimantList = new List<Claimant>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                //cmd.CommandText = "SELECT EmployeeMaster FROM B2E WHERE Section = (SELECT Section FROM B2E WHERE EmployeeMaster = @EmployeeMaster) AND AccessLevel = 'EMP';";
                cmd.CommandText = @"
SELECT EmployeeMaster FROM Claim 
INNER JOIN B2E 
ON Claim.EntryOwner = B2E.EmployeeMaster
WHERE Status = 6 AND B2E.Section = (SELECT Section FROM B2E WHERE EmployeeMaster = @EmployeeMaster) AND AccessLevel = 'EMP'
GROUP BY EmployeeMaster
HAVING COUNT(Claim.Id) > 0";
                cmd.Parameters.AddWithValue("@EmployeeMaster", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimantList = ds.Tables[0].AsEnumerable().Select(r => new Claimant
                    {
                        EmployeeMaster = r.Field<string>("EmployeeMaster")
                    }).ToList();
                }
            }

            return claimantList;
        }

        public static List<string> GetSectionListSubmittedToHR()
        {
            List<string> sectionList = null;
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                //cmd.CommandText = "SELECT DISTINCT Section FROM Claim WHERE Status = @ClaimStatus;";
                cmd.CommandText = @"SELECT  DISTINCT Section FROM Claim c
INNER JOIN B2E b
ON c.EntryOwner = b.EmployeeMaster
WHERE ReportingPeriodId = (SELECT Id FROM ReportingPeriod WHERE Locked = 1) AND c.Status = @ClaimStatus
ORDER BY b.Section";

                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.SUBMITTED_TO_HR);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                sectionList = ds.Tables[0].AsEnumerable().Select(r => r.Field<string>("Section")).ToList();
            }

            return sectionList;
        }

        public static List<Claimant> GetClaimantListSubmittedToHR()
        {
            List<Claimant> claimantList = new List<Claimant>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                //cmd.CommandText = "SELECT DISTINCT EntryOwner FROM Claim WHERE Status = @ClaimStatus;";
                cmd.CommandText = "SELECT DISTINCT B2E.NameLegal, EntryOwner  FROM Claim LEFT JOIN B2E ON Claim.EntryOwner = B2E.EmployeeMaster WHERE ReportingPeriodId = (SELECT Id FROM ReportingPeriod WHERE Locked = 1) AND Status = @ClaimStatus ORDER BY B2E.NameLegal";
                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.SUBMITTED_TO_HR);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimantList = ds.Tables[0].AsEnumerable().Select(r => new Claimant
                    {
                        EmployeeMaster = r.Field<string>("EntryOwner")
                    }).ToList();
                }
            }

            return claimantList;
        }

        public static List<Claimant> GetAllClaimantInSectionList(string section)
        {
            return GetAllClaimantInSectionList(section, string.Empty);
        }

        public static List<Claimant> GetAllClaimantInSectionList(string section, string whereClause)
        {
            List<Claimant> claimantList = new List<Claimant>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT DISTINCT EntryOwner FROM Claim WHERE Status > @ClaimStatus;";
                cmd.CommandText = @"SELECT  DISTINCT EntryOwner 
FROM    Claim 
WHERE   Status > @ClaimStatus AND EntryOwner IN (SELECT EmployeeMaster FROM B2E WHERE Section = @B2E_Section) " + whereClause;

                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.DRAFT);
                cmd.Parameters.AddWithValue("@B2E_Section", section);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimantList = ds.Tables[0].AsEnumerable().Select(r => new Claimant
                    {
                        EmployeeMaster = r.Field<string>("EntryOwner")
                    }).ToList();
                }
            }

            return claimantList;
        }

        public static List<Claimant> GetSecretaryAllClaimantInSectionList(string section)
        {
            List<Claimant> claimantList = new List<Claimant>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT DISTINCT EntryOwner FROM Claim WHERE Status = @ClaimStatus;";
                cmd.CommandText = @"SELECT  DISTINCT EntryOwner 
FROM    Claim 
WHERE   Status = @ClaimStatus AND EntryOwner IN (SELECT EmployeeMaster FROM B2E WHERE Section = @B2E_Section)";

                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.SUBMITTED_TO_RO);
                cmd.Parameters.AddWithValue("@B2E_Section", section);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimantList = ds.Tables[0].AsEnumerable().Select(r => new Claimant
                    {
                        EmployeeMaster = r.Field<string>("EntryOwner")
                    }).ToList();
                }
            }

            return claimantList;
        }

        public static List<Claimant> GetAllClaimantList()
        {
            List<Claimant> claimantList = new List<Claimant>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT DISTINCT EntryOwner FROM Claim WHERE Status > @ClaimStatus;";
                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.DRAFT);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimantList = ds.Tables[0].AsEnumerable().Select(r => new Claimant
                    {
                        EmployeeMaster = r.Field<string>("EntryOwner")
                    }).ToList();
                }
            }

            return claimantList;
        }

        public static List<Claimant> GetAllClaimantList(string whereClause)
        {
            List<Claimant> claimantList = new List<Claimant>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT DISTINCT B2E.NameLegal, EntryOwner FROM Claim LEFT JOIN B2E ON Claim.EntryOwner = B2E.EmployeeMaster WHERE Status > @ClaimStatus " + whereClause + " ORDER BY B2E.NameLegal";
                cmd.Parameters.AddWithValue("@ClaimStatus", ClaimStatus.DRAFT);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimantList = ds.Tables[0].AsEnumerable().Select(r => new Claimant
                    {
                        EmployeeMaster = r.Field<string>("EntryOwner")
                    }).ToList();
                }
            }

            return claimantList;
        }

        public static RejectionDetail GetRejectionDetail(string id)
        {
            RejectionDetail detail = null;

            // TODO: retrieve rejection detail based on id
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Claim WHERE Id = @Id;";
                cmd.Parameters.AddWithValue("@ID", id);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    detail = ds.Tables[0].AsEnumerable().Select(r => new RejectionDetail
                    {
                        RejectorId = r.Field<string>("RejectorId"), 
                        RejectDate = r.Field<DateTime?>("RejectorDate"), 
                        RejectReason = r.Field<string>("RejectorReason")
                    }).FirstOrDefault();
                }
            }

            return detail;
        }

        #region possibly not used

        public DataTable GetClaimSummaryDataTable(string domainName)
        {
            DataTable dtResult = null;
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = 
@"SELECT    SUM(ISNULL(OT1_5, 0))   AS 'OT1_5', 
            SUM(ISNULL(OT2_0, 0))   AS 'OT2_0', 
            SUM(ISNULL(CONVERT(TINYINT, PH), 0)) AS 'PH',
            SUM(ISNULL(CONVERT(TINYINT, RD4), 0)) AS 'RD4',
            SUM(ISNULL(CONVERT(TINYINT, RD8), 0)) AS 'RD8',
            SUM(ISNULL(AlwMeal, 0)) AS 'AlwMeal', 
            SUM(ISNULL(AlwShft, 0)) AS 'AlwShft'
FROM    Claim 
WHERE EntryOwner = @EntryOwner AND Status = 0;";
            cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    dtResult = ds.Tables[0];
                }
            }

            return dtResult;
        }

        #endregion possibly not used

        #region helper functions
        public static void AddParameters(SqlParameterCollection collection, string parameterName, DateTime? nullableType)
        {
            if (collection.Contains(parameterName))
                return;

            if (nullableType.HasValue)
            {
                collection.AddWithValue(parameterName, nullableType.Value);
            }
            else
            {
                collection.AddWithValue(parameterName, DBNull.Value);
            }
        }

        public static void AddParameters(SqlParameterCollection collection, string parameterName, decimal? nullableType)
        {
            if (nullableType.HasValue)
            {
                collection.AddWithValue(parameterName, nullableType.Value);
            }
            else
            {
                collection.AddWithValue(parameterName, DBNull.Value);
            }
        }

        public static void AddParameters(SqlParameterCollection collection, string parameterName, bool? nullableType)
        {
            if (nullableType.HasValue)
            {
                collection.AddWithValue(parameterName, nullableType.Value.ToString());
            }
            else
            {
                collection.AddWithValue(parameterName, DBNull.Value);
            }
        }

        public static void AddParameters(SqlParameterCollection collection, string parameterName, string nullableType)
        {
            if (nullableType == null)
            {
                collection.AddWithValue(parameterName, DBNull.Value);
            }
            else
            {
                collection.AddWithValue(parameterName, nullableType);
            }
        }
        #endregion helper functions

        public static bool AddClaim(string domainName, KeppelLand.Entities.Claim c)
        {
            bool result = false;
            System.Data.DataSet ds = new DataSet("Claims");

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                cmd.Parameters.AddWithValue("@ClaimDate", c.ClaimDate);

                //cmd.CommandText = "SELECT * FROM Claim WHERE EntryOwner = @EntryOwner AND Status = 6 AND YEAR(ClaimDate) = YEAR(@ClaimDate) AND MONTH(ClaimDate) = MONTH(@ClaimDate);";
                //da.Fill(ds, "ClaimedMonthCheck");
                //if (ds.Tables["ClaimedMonthCheck"].Rows.Count > 0)
                //{
                //    throw new ClaimException("You have already claim overtime for specific month");
                //}

                if (c.AlwMeal.HasValue && c.AlwMeal.Value > 0)
                {
                    cmd.CommandText = "SELECT COUNT(*) AS 'Count' FROM Claim WHERE EntryOwner = @EntryOwner AND ClaimDate = @ClaimDate AND AlwMeal > 0;";
                    da.Fill(ds, "MealAllowanceCheck");
                    if ((int)ds.Tables["MealAllowanceCheck"].Rows[0]["Count"] > 0)
                    {
                        throw new ClaimException("You have already claim meal allowance for this date");
                    }
                }

                if (c.StartOverTime.HasValue && c.EndOverTime.HasValue)
                {
                    cmd.CommandText =
@"SELECT  StartOverTime, EndOverTime, CONVERT(DATETIME, @StartOverTime), CONVERT(DATETIME, @EndOverTime)
FROM    Claim 
WHERE   EntryOwner = @EntryOwner
        AND ClaimDate = @ClaimDate
        AND (
            (@StartOverTime >= StartOverTime AND @EndOverTime <= EndOverTime)
        OR  (@StartOverTime <= StartOverTime AND @EndOverTime > StartOverTime AND @EndOverTime < EndOverTime)
        OR  (@EndOverTime  >= StartOverTime AND @StartOverTime > StartOverTime AND @StartOverTime < EndOverTime)
        OR  (@StartOverTime <= StartOverTime AND @EndOverTime >= EndOverTime)
    )";
                    AddParameters(cmd.Parameters, "@StartOverTime", c.StartOverTime);
                    AddParameters(cmd.Parameters, "@EndOverTime", c.EndOverTime);

                    da.Fill(ds, "OverlappingHoursCheck");
                    if ((int)ds.Tables["OverlappingHoursCheck"].Rows.Count > 0)
                    {
                        throw new ClaimException("Your overtime hours overlaps an existing entry.");
                    }
                }

                cmd.CommandText = "INSERT INTO Claim (EntryOwner, EntryDateTime, ClaimDate, StartWorkTime, EndWorkTime, StartOverTime, EndOverTime, OT1_5, OT2_0, PH, RD4, RD8, AlwShft, AlwMeal, ShiftType, Remark, BreakHours) VALUES (@EntryOwner, @EntryDateTime, @ClaimDate, @StartWorkTime, @EndWorkTime, @StartOverTime, @EndOverTime, @OT1_5, @OT2_0, @PH, @RD4, @RD8, @AlwShft, @AlwMeal, @ShiftType, @Remark, @BreakHours);";
                AddParameters(cmd.Parameters, "@EntryDateTime", UserIdentityHelper.GetInputDate());
                AddParameters(cmd.Parameters, "@StartWorkTime", c.StartWorkTime);
                AddParameters(cmd.Parameters, "@EndWorkTime", c.EndWorkTime);
                AddParameters(cmd.Parameters, "@StartOverTime", c.StartOverTime);
                AddParameters(cmd.Parameters, "@EndOverTime", c.EndOverTime);
                AddParameters(cmd.Parameters, "@OT1_5", c.OT1_5);
                AddParameters(cmd.Parameters, "@OT2_0", c.OT2_0);
                AddParameters(cmd.Parameters, "@PH", c.PH);
                AddParameters(cmd.Parameters, "@RD4", c.RD4);
                AddParameters(cmd.Parameters, "@RD8", c.RD8);
                AddParameters(cmd.Parameters, "@AlwShft", c.AlwShft);
                AddParameters(cmd.Parameters, "@AlwMeal", c.AlwMeal);
                AddParameters(cmd.Parameters, "@ShiftType", c.ShiftType);
                AddParameters(cmd.Parameters, "@Remark", c.Remark);
                AddParameters(cmd.Parameters, "@BreakHours", c.BreakHours);


                conn.Open();
                cmd.ExecuteNonQuery();
                result = true;
            }

            return result;
        }

        public static bool UpdateClaim(string id, KeppelLand.Entities.Claim c)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "UPDATE Claim SET ClaimDate = @ClaimDate, StartWorkTime = @StartWorkTime, EndWorkTime = @EndWorkTime, StartOverTime = @StartOverTime, EndOverTime = @EndOverTime, OT1_5 = @OT1_5, OT2_0 = @OT2_0, PH = @PH, RD4 = @RD4, RD8 = @RD8, AlwShft = @AlwShft, AlwMeal = @AlwMeal, ShiftType = @ShiftType, Remark = @Remark WHERE Id = @Id AND Status = 0;";
                AddParameters(cmd.Parameters, "@Id", id);
                cmd.Parameters.AddWithValue("@ClaimDate", c.ClaimDate);
                AddParameters(cmd.Parameters, "@StartWorkTime", c.StartWorkTime);
                AddParameters(cmd.Parameters, "@EndWorkTime", c.EndWorkTime);
                AddParameters(cmd.Parameters, "@StartOverTime", c.StartOverTime);
                AddParameters(cmd.Parameters, "@EndOverTime", c.EndOverTime);
                AddParameters(cmd.Parameters, "@OT1_5", c.OT1_5);
                AddParameters(cmd.Parameters, "@OT2_0", c.OT2_0);
                AddParameters(cmd.Parameters, "@PH", c.PH);
                AddParameters(cmd.Parameters, "@RD4", c.RD4);
                AddParameters(cmd.Parameters, "@RD8", c.RD8);
                AddParameters(cmd.Parameters, "@AlwShft", c.AlwShft);
                AddParameters(cmd.Parameters, "@AlwMeal", c.AlwMeal);
                AddParameters(cmd.Parameters, "@ShiftType", c.ShiftType);
                AddParameters(cmd.Parameters, "@Remark", c.Remark);

                conn.Open();
                cmd.ExecuteNonQuery();
                result = true;
            }

            return result;
        }

        public static bool RemoveClaim(string domainName, string id)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM Claim WHERE Status = 0 AND Id = @Id AND EntryOwner = @EntryOwner;";
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                cmd.Parameters.AddWithValue("@Id", id);

                conn.Open();
                cmd.ExecuteNonQuery();
                result = true;
            }

            return result;
        }

        public static bool RetrieveClaim(string domainName, string selected_id)
        {
            bool result = false;

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"UPDATE Claim SET Status = @NewStatus, SubmissionDate = NULL, RecommenderId = NULL, RecommendDate = NULL, ApproverId = NULL, ApproveDate = NULL
WHERE EntryOwner = @EmployeeMaster AND Id = @EntryId AND ((Status = @RecommendStatus) OR (Status = @ApproveStatus AND RecommenderId IS NULL));";

                cmd.Parameters.AddWithValue("@NewStatus", KeppelLand.Entities.ClaimStatus.DRAFT);
                cmd.Parameters.AddWithValue("@EmployeeMaster", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                cmd.Parameters.AddWithValue("@EntryId", selected_id);
                
                cmd.Parameters.AddWithValue("@RecommendStatus", KeppelLand.Entities.ClaimStatus.SUBMITTED_TO_RO);
                cmd.Parameters.AddWithValue("@ApproveStatus", KeppelLand.Entities.ClaimStatus.SUBMITTED_TO_AO);

                conn.Open();
                cmd.ExecuteNonQuery();
                result = true;
            }

            return result;
        }

        public static bool SubmitClaimsToSupervisor(string domainName)
        {
            bool result = false;
            string recommender = string.Empty;
            string approver = string.Empty;
            System.Data.DataSet ds = new DataSet("ReportingStructure");

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(cmd);
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                cmd.CommandText =
@"SELECT  EmployeeMaster, COALESCE(RecommenderId2, RecommenderId1) AS Recommender, COALESCE(ApproverId2, ApproverId1) AS Approver
FROM    ReportingStructure 
WHERE   EmployeeMaster = @EntryOwner;";

                da.Fill(ds, "ReportingStructure");

                if (ds.Tables["ReportingStructure"].Rows.Count > 0)
                {
                    recommender = ds.Tables["ReportingStructure"].Rows[0]["Recommender"].ToString().Trim();
                    approver = ds.Tables["ReportingStructure"].Rows[0]["Approver"].ToString().Trim();
                }

                if (UserIdentityHelper.IsTraining()) // overrides if training
                {
                    recommender = string.Empty;
                    approver = UserIdentityHelper.GetTrainingSupervisorName();
                    if (!String.IsNullOrEmpty(approver))
                    {
                        approver = KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(approver);
                    }
                }

                conn.Open();
                
                if (!string.IsNullOrEmpty(recommender))
                {
                    cmd.CommandText = "UPDATE Claim SET Status = @NewStatus, RecommenderId = @RecommenderId, SubmissionDate = @SubmissionDate, RejectorId = NULL, RejectDate = NULL, RejectReason = NULL WHERE Status = @CurrentStatus AND EntryOwner = @EntryOwner;";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@NewStatus", ClaimStatus.SUBMITTED_TO_RO);
                    cmd.Parameters.AddWithValue("@CurrentStatus", ClaimStatus.DRAFT);
                    cmd.Parameters.AddWithValue("@RecommenderId", recommender);
                    cmd.Parameters.AddWithValue("@SubmissionDate", UserIdentityHelper.GetInputDate());
                    cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                    cmd.ExecuteNonQuery();
                    result = true;
                }
                else if (!string.IsNullOrEmpty(approver))
                {
                    cmd.CommandText = "UPDATE Claim SET Status = @NewStatus, ApproverId = @ApproverId, SubmissionDate = @SubmissionDate, RejectorId = NULL, RejectDate = NULL, RejectReason = NULL WHERE Status = @CurrentStatus AND EntryOwner = @EntryOwner;";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@NewStatus", ClaimStatus.SUBMITTED_TO_AO);
                    cmd.Parameters.AddWithValue("@CurrentStatus", ClaimStatus.DRAFT);
                    cmd.Parameters.AddWithValue("@ApproverId", approver);
                    cmd.Parameters.AddWithValue("@SubmissionDate", UserIdentityHelper.GetInputDate());
                    cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                    cmd.ExecuteNonQuery();
                    result = true;
                }
                else
                {
                    //cmd.CommandText = "UPDATE Claim SET Status = @NewStatus, SubmissionDate = @SubmissionDate, RejectorId = NULL, RejectDate = NULL, RejectReason = NULL WHERE Status = @CurrentStatus AND EntryOwner = @EntryOwner;";
                    //cmd.Parameters.Clear();
                    //cmd.Parameters.AddWithValue("@NewStatus", ClaimStatus.SUBMITTED_TO_HR);
                    //cmd.Parameters.AddWithValue("@CurrentStatus", ClaimStatus.DRAFT);
                    //cmd.Parameters.AddWithValue("@SubmissionDate", UserIdentityHelper.GetInputDate());
                    //cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                    //cmd.ExecuteNonQuery();
                    result = false;
                }

                
            }

            return result;
        }

        public static bool RejectClaims(string domainName, string rejectionReason, string selected_id)
        {
            bool result = false;

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"UPDATE Claim SET Status = @NewStatus, RejectorId = @EmployeeMaster, RejectDate = @RejectDate, RejectReason = @RejectReason, SubmissionDate = NULL, RecommenderId = NULL, RecommendDate = NULL, ApproverId = NULL, ApproveDate = NULL 
                WHERE ((RecommenderId = @EmployeeMaster AND Status = @RecommendStatus) OR (ApproverId = @EmployeeMaster AND Status = @ApproveStatus))
                AND Id IN (SELECT data FROM dbo.fnSplitString(@IdList,','));";

                cmd.Parameters.AddWithValue("@NewStatus", KeppelLand.Entities.ClaimStatus.DRAFT);
                cmd.Parameters.AddWithValue("@EmployeeMaster", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                cmd.Parameters.AddWithValue("@RejectDate", UserIdentityHelper.GetInputDate());
                cmd.Parameters.AddWithValue("@RejectReason", rejectionReason.Replace("\r\n", "<br />"));
                cmd.Parameters.AddWithValue("@RecommendStatus", KeppelLand.Entities.ClaimStatus.SUBMITTED_TO_RO);
                cmd.Parameters.AddWithValue("@ApproveStatus", KeppelLand.Entities.ClaimStatus.SUBMITTED_TO_AO);
                cmd.Parameters.AddWithValue("@IdList", selected_id);

                conn.Open();
                cmd.ExecuteNonQuery();
                result = true;
            }

            return result;
        }

        public static List<ClaimPeriod> GetClaimPeriodList(string domainName, ClaimStatus status)
        {
            List<ClaimPeriod> claimList = new List<ClaimPeriod>();
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"
SELECT  DISTINCT
        YEAR(c.ClaimDate) AS 'Year',
        MONTH(c.ClaimDate) AS 'Month',
        CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR,c.ClaimDate,112), 1, 6) + '01') AS 'LabelDate'
FROM    Claim c
WHERE   EntryOwner = @EntryOwner
        AND Status  = @Status
ORDER BY CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR,c.ClaimDate,112), 1, 6) + '01') DESC";
                cmd.Parameters.AddWithValue("@EntryOwner", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));
                cmd.Parameters.AddWithValue("@Status", status);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    claimList = ds.Tables[0].AsEnumerable().Select(r => new ClaimPeriod
                    {
                        Year = r.Field<int>("Year"),
                        Month = r.Field<int>("Month"),
                        LabelDate = r.Field<DateTime>("LabelDate")
                    }).ToList();
                }
            }

            return claimList;
        }

        public static bool HRClearClaim(string selected_id)
        {
            bool result = false;

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                conn.Open();
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"
UPDATE  Claim
SET     HRClearedDate = @HRClearedDate,
        Status = @NewStatus
FROM    Claim c
WHERE   c.Id IN (SELECT data FROM dbo.fnSplitString(@IdList,','))
        AND c.Status = @CurrentStatus;";

                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@HRClearedDate", KeppelLand.Helpers.UserIdentityHelper.GetInputDate());
                cmd.Parameters.AddWithValue("@NewStatus", KeppelLand.Entities.ClaimStatus.CLEARED_BY_HR);
                cmd.Parameters.AddWithValue("@CurrentStatus", KeppelLand.Entities.ClaimStatus.SUBMITTED_TO_HR);
                cmd.Parameters.AddWithValue("@IdList", selected_id);
                cmd.ExecuteNonQuery();

                result = true;
            }

            return result;
        }

        public static bool ApproveClaim(string selected_id)
        {
            bool result = false;

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                conn.Open();
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();

                // Update claims currently submitted to AO
                // Set Claim Status to be submitted to HR
                cmd.CommandText = @"
UPDATE  Claim
SET     ApproveDate = @ApproveDate,
        Status = @NewStatus
FROM    Claim c
WHERE   c.Id IN (SELECT data FROM dbo.fnSplitString(@IdList,','))
        AND c.Status = @CurrentStatus;";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@ApproveDate", KeppelLand.Helpers.UserIdentityHelper.GetInputDate());
                cmd.Parameters.AddWithValue("@NewStatus", KeppelLand.Entities.ClaimStatus.SUBMITTED_TO_HR);
                cmd.Parameters.AddWithValue("@CurrentStatus", KeppelLand.Entities.ClaimStatus.SUBMITTED_TO_AO);
                cmd.Parameters.AddWithValue("@IdList", selected_id);
                cmd.ExecuteNonQuery();

                if (!UserIdentityHelper.IsTraining())
                {
                    // Update claims currently submitted to RO that does not have AO
                    // Set Claim Status to be submitted to HR
                    cmd.CommandText = @"
UPDATE  Claim
SET     RecommendDate = @RecommendDate,
        Status = @NewStatus,
        ApproverId = NULL
FROM    Claim c
INNER JOIN
        ReportingStructure rs
        ON c.EntryOwner = rs.EmployeeMaster
WHERE   c.Id IN (SELECT data FROM dbo.fnSplitString(@IdList,','))
        AND c.Status = @CurrentStatus
        AND COALESCE(rs.ApproverId2, rs.ApproverId1) IS NULL";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@RecommendDate", KeppelLand.Helpers.UserIdentityHelper.GetInputDate());
                    cmd.Parameters.AddWithValue("@NewStatus", KeppelLand.Entities.ClaimStatus.SUBMITTED_TO_HR);
                    cmd.Parameters.AddWithValue("@CurrentStatus", KeppelLand.Entities.ClaimStatus.SUBMITTED_TO_RO);
                    cmd.Parameters.AddWithValue("@IdList", selected_id);
                    cmd.ExecuteNonQuery();

                    // Update claims currently submitted to RO that has AO
                    // Set Claim Status to be submitted to AO
                    cmd.CommandText = @"
UPDATE  Claim
SET     RecommendDate = @RecommendDate,
        Status = @NewStatus,
        ApproverId = COALESCE(rs.ApproverId2, rs.ApproverId1)
FROM    Claim c
INNER JOIN
        ReportingStructure rs
        ON c.EntryOwner = rs.EmployeeMaster
WHERE   c.Id IN (SELECT data FROM dbo.fnSplitString(@IdList,','))
        AND c.Status = @CurrentStatus
        AND COALESCE(rs.ApproverId2, rs.ApproverId1) IS NOT NULL;";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@RecommendDate", KeppelLand.Helpers.UserIdentityHelper.GetInputDate());
                    cmd.Parameters.AddWithValue("@NewStatus", KeppelLand.Entities.ClaimStatus.SUBMITTED_TO_AO);
                    cmd.Parameters.AddWithValue("@CurrentStatus", KeppelLand.Entities.ClaimStatus.SUBMITTED_TO_RO);
                    cmd.Parameters.AddWithValue("@IdList", selected_id);
                    cmd.ExecuteNonQuery();
                
                }

                result = true;
            }

            return result;
        }

        public static string RejectClaimEmailContent(DataTable dtRejectedClaims, string rejectionReason, string rejector)
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            // Form content of the rejection email.
            //sb.AppendLine("To the recipients of this e-mail,");
            sb.AppendLine();
            sb.AppendFormat("<p>Please note that {0} have rejected the following claims.", rejector);
            sb.AppendLine();

            sb.AppendLine("<p>Records rejected: " + dtRejectedClaims.Rows.Count.ToString());

            if (dtRejectedClaims.Rows.Count > 0)
            {
                sb.AppendLine("<table class=\"table table-striped table-bordered\" id=\"claim_list\" border=\"1\" cellspacing=\"1\" >");
                sb.AppendLine("    <thead>");
                sb.AppendLine("        <tr>");
                sb.AppendLine("            <th rowspan=\"2\">Claim date</th>");
                sb.AppendLine("            <th rowspan=\"2\" style=\"width:180px\">Shift Type</th>");
                sb.AppendLine("            <th colspan=\"2\" class=\"text-center\">Official Hours</th>");
                sb.AppendLine("            <th colspan=\"2\" class=\"text-center\">Overtime Hours</th>");
                sb.AppendLine("            <th rowspan=\"2\" class=\"text-center\">Total OT Hours</th>");
                sb.AppendLine("");
                sb.AppendLine("            <th colspan=\"2\" class=\"text-center\">OT Rate</th>");
                sb.AppendLine("            <th rowspan=\"2\" style=\"cursor:default;\" title=\"Public Holiday\">PH</th>");
                sb.AppendLine("            <th colspan=\"2\" class=\"text-center\" style=\"white-space:nowrap;\">Rest Day</th>");
                sb.AppendLine("            <th rowspan=\"2\" style=\"width:86px;\">Meal<br />Alw</th>");
                sb.AppendLine("            <th rowspan=\"2\" style=\"width:86px;\">Shift<br />Alw</th>");
                sb.AppendLine("            ");
                sb.AppendLine("            <th rowspan=\"2\">Remark</th>");
                sb.AppendLine("        </tr>");
                sb.AppendLine("        <tr>");
                sb.AppendLine("            <th style=\"white-space:nowrap;width:82px;\">Start time</th>");
                sb.AppendLine("            <th style=\"white-space:nowrap;width:82px;\">End time&nbsp;</th>");
                sb.AppendLine("            <th style=\"white-space:nowrap;\">Start time</th>");
                sb.AppendLine("            <th style=\"white-space:nowrap;\">End time&nbsp;</th>");
                sb.AppendLine("            <th style=\"width:86px;white-space:nowrap;\">1.5 X</th>");
                sb.AppendLine("            <th style=\"width:86px;white-space:nowrap;\">2.0 X</th>");
                sb.AppendLine("            <th style=\"width:36px;\">&lt;4</th>");
                sb.AppendLine("            <th style=\"width:36px;\">&gt;4</th>");
                sb.AppendLine("        </tr>");
                sb.AppendLine("    </thead>");
                sb.AppendLine("    <tbody>");

                List<KeppelLand.Entities.Claim> rejectedClaimList = KeppelLand.Controllers.ClaimController.ClaimDataTableToList(dtRejectedClaims);
                foreach (KeppelLand.Entities.Claim c in rejectedClaimList)
                {
                    sb.AppendLine("<tr>");
                    sb.AppendFormat("    <td style=\"width:1%;white-space:nowrap;\">{0} ({1})</td>", FormatHelper.FormatDate(c.ClaimDate), FormatHelper.FormatDayOfWeek(c.ClaimDate));
                    sb.AppendFormat("    <td style=\"width:180px\">{0}</td>", c.ShiftType);
                    sb.AppendFormat("    <td style=\"width:1%\">{0}</td>", FormatHelper.FormatTime(c.StartWorkTime));
                    sb.AppendFormat("    <td style=\"width:1%\">{0}</td>", FormatHelper.FormatTime(c.EndWorkTime));
                    sb.AppendFormat("    <td style=\"width:1%\">{0}</td>", FormatHelper.FormatTime(c.StartOverTime));
                    sb.AppendFormat("    <td style=\"width:1%\">{0}</td>", FormatHelper.FormatTime(c.EndOverTime));
                    sb.AppendFormat("    <td style=\"width:1%\" class=\"text-right\">{0}</td>", c.TotalOTHours);
                    sb.AppendLine("");
                    sb.AppendFormat("    <td style=\"width:1%\" class=\"text-right\">{0}</td>", FormatHelper.FormatDecimal(c.OT1_5));
                    sb.AppendFormat("    <td style=\"width:1%\" class=\"text-right\">{0}</td>", FormatHelper.FormatDecimal(c.OT2_0));
                    sb.AppendFormat("    <td style=\"width:1%; text-align:center; width:36px;\">{0}</td>", FormatHelper.FormatCheckmark(c.PH));
                    sb.AppendFormat("    <td style=\"width:1%; text-align:center; width:36px;\">{0}</td>", FormatHelper.FormatCheckmark(c.RD4));
                    sb.AppendFormat("    <td style=\"width:1%; text-align:center; width:36px;\">{0}</td>", FormatHelper.FormatCheckmark(c.RD8));
                    sb.AppendFormat("    <td style=\"width:1%\" class=\"text-right\">{0}</td>", FormatHelper.FormatMoney(c.AlwMeal));
                    sb.AppendFormat("    <td style=\"width:1%\" class=\"text-right\">{0}</td>", FormatHelper.FormatMoney(c.AlwShft));
                    sb.AppendFormat("    <td>{0}</td>", c.Remark);
                    sb.AppendLine("</tr>");
                }

                sb.AppendLine("    </tbody>");
                sb.AppendLine("</table>");

            }

            sb.AppendLine();
            sb.AppendLine("<p>Reason for rejection are as follows:<br/>");
            sb.AppendLine(rejectionReason);
            sb.AppendLine(string.Format("<p>Claims rejected by {0}.", rejector));
            sb.AppendLine();
            sb.AppendLine("<p>E-mail sent via e-overtime system.");

            return sb.ToString();
        }
    }
}